Generated code - Entity collection and Typed List/Typed View paging, SelfServicing
Preface
Paging is the way to browse through a list of objects or rows of data one
page at a time. This can be handy when you have thousands of rows / objects
matching search criteria but you want to enlist only a small number at once.
With the paging functionality build into entity collection classes and typed
list/typed view classes, you can tell the generated code which page to
retrieve, instead of getting all the results at once. This section describes
the various options available to you.
Paging using an entity collection fetch
Paging through an entity collection is implemented in an overload of
entityCollection.GetMulti(). The particular overload accepts the page size, which is the number of objects to
retrieve in the fetch action, and the page number to retrieve. If you for example pass 10 for the page size and 4 for the page number, you'll
get record number 31-40,
the first record is 1, the first page is also numbered 1. Paging is disabled if you pass 0 for the page number or 0 or 1 for the page size.
Get the total number of objects
When using paging, it's often required to know how many pages a given
resultset contains.For example, you want to show a list of page numbers the user can choose from, like
Google uses. You can retrieve the number of objects matching your filter by using the entity collection method
entityCollection.GetDbCount().
the method has overloads to support filtering spanning multiple entities as well. Below is an example to retrieve the total number of different order objects of
customers from France.
// C#
OrderCollection orders = new OrderCollection();
PredicateExpression filter = new PredicateExpression(CustomerFields.Country == "France");
RelationCollection relations = new RelationCollection();
relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId);
int amount = orders.GetDbCount(filter, relations);
' VB.NET
Dim orders As New OrderCollection()
Dim filter As New PredicateExpression(CustomerFields.Country = "France")
Dim relations As New RelationCollection()
relations.Add(OrderEntity.Relations.CustomerEntityUsingCustomerId)
Dim amount As Integer = orders.GetDbCount(filter, relations)
The number is retrieved by using the GetScalar() method of the entity collection class, using the Count(*) aggregate function. See for more details about
using Aggregate functions in your code the section
Field expressions and aggregates.
The value in
amount can now be used to calculate the total number of pages when the page size is given: number of pages = (number of objects / pagesize) + n, where n is either 0 (total number of objects modulo pagesize is 0) or 1 (total number of objects modulo pagesize > 0).
Below is the code to retrieve page 4, with a pagesize of 10 objects. We re-use the filter objects used in the GetDbCount() call:
// C#
orders.GetMulti(filter, 0, null, relations, 4, 10);
// QuerySpec alternative
var qf = new QueryFactory();
var q = qf.Order.Page(4, 10);
orders.GetMulti(q);
' VB.NET
orders.GetMulti(filter, 0, Nothing, relations, 4, 10)
' QuerySpec alternative
Dim qf As New QueryFactory()
Dim q = qf.Order.Page(4, 10)
orders.GetMulti(q)
After this call, orders contains 10 objects, which formed the 4th page in the result set matching the filter defined. No sorting is applied here, but if you
specify a sort expression, the sorting is performed prior to the paging logic.
It's recommended to use a sorter
in your query if you use paging to be sure the data is ordered in a
predictable fashion. SQL by definition applies no ordering on SELECT
resultsets.
Paging using a TypedList or TypedView fetch
The paging functionality is also available for typed list and typed view classes, through an overload of the
Fill() method. For typed lists and
typed views, the same definitions are valid as for collections: page numbers start at 1, the first record is numbered 1 and paging is disabled
if you pass in a page number of 0 or you pass in a page size of 0 or 1.
Get the total number of rows
Getting the total number of rows for a typed list or typed view works the same as it is done for a collection. A typed list and typed view have
a method called
GetDbCount() and various overloads accept a filter, and for typed lists als a RelationCollection.
You can also specify if the
GetDbCount() should take into account duplicate rows or not.
Fetching a given page in the typed list or typed view is then boiling down to using the
Fill() overload which accepts the two paging parameters. Be sure to
clear the typed list/typed view object before calling
Fill() again to fetch another page.